﻿using System;
using System.Data;
using Innovative.Data;

namespace Demo_Console
{
	class Program
	{
		static void Main(string[] args)
		{
			Program.LoadExcel2003();
			Console.WriteLine();
			Program.LoadExcel2013();

			// ***			
			// *** For for the user to quit
			// ***
			Console.WriteLine("Press any key to quit...");
			Console.ReadKey();
		}

		static void LoadExcel2003()
		{
			// ***
			// *** Create an instance of ExcelQuery with
			// *** the sample data file.
			// ***
			string path = @"|DataDirectory|\SampleData2003.xls";
			ExcelQuery excelQuery = new ExcelQuery(path);

			// ***
			// *** Set some properties...
			// ***
			excelQuery.HeaderHasFieldNames = true;
			excelQuery.Provider = ExcelQuery.ConnectionStringItems.Providers.Jet4;
			excelQuery.ExcelVersion = ExcelQuery.ConnectionStringItems.ExcelVersions.Excel97to2003;
			excelQuery.ConnectionProperties = "IMEX=0";

			Console.WriteLine("Data from Excel 2003 File");
			Program.DisplayData(excelQuery);			
		}

		static void LoadExcel2013()
		{
			// ***
			// *** Create an instance of ExcelQuery with
			// *** the sample data file.
			// ***
			string path = @"|DataDirectory|\SampleData.xlsx";
			ExcelQuery excelQuery = new ExcelQuery(path);

			// ***
			// *** Set some properties...
			// ***
			excelQuery.HeaderHasFieldNames = true;
			excelQuery.Provider = ExcelQuery.ConnectionStringItems.Providers.Jet12;
			excelQuery.ExcelVersion = ExcelQuery.ConnectionStringItems.ExcelVersions.Excel2007to2013;
			excelQuery.ConnectionProperties = "IMEX=0";

			Console.WriteLine("Data from Excel 2013 File");
			Program.DisplayData(excelQuery);
		}

		static void DisplayData(ExcelQuery excelQuery)
		{
			// ***
			// *** Define a SQL query against the first sheet called Data
			// ***
			string sql = "SELECT * FROM [Data$]";

			// ***
			// *** Execute the query and get a DataSet
			// ***
			DataSet data = excelQuery.ExecuteDataSet(sql, "SampleData");

			// ***
			// *** Display the data
			// ***
			Console.WriteLine("Name\t\tBirthday\t\tAge");

			foreach (var item in data.Tables["SampleData"].AsEnumerable())
			{
				Console.WriteLine("{0}\t\t{1}\t\t{2}", 
						item.Field<string>("Name"), 
						item.Field<DateTime>("Birthday").Date.ToShortDateString(), 
						item.Field<double>("Age").ToString("##.0"));
			}
		}
	}
}
